package org.alive.test.deadlock;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.alive.dao.BaseDAO;
import org.alive.dao.DsUtil;
import org.alive.util.RandomUtil;

public class TaskDAO extends BaseDAO {

	/**
	 * Get acctNo for test.
	 * 
	 * @return
	 */
	public String getAvaliableAcctNo(int romnum) {
		Connection conn = DsUtil.getInstance().getConnection();
		if (conn == null) {
			throw new RuntimeException(
					"Fatal error. Cann't get jdbc Connection...");
		}
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT C.ACCT_NO FROM AIP_CUST_INFO C, AIP_ACCT_ACCOUNT A  ");
		sbSql.append("	WHERE C.ACCT_STAT = ? AND A.STAT = ?  ");
		sbSql.append("	  AND C.ACCT_NO = A.ACCT_NO AND ROWNUM <= ? ");
		PreparedStatement ps = null;
		ResultSet rs = null;
		String acctNo = null;
		try {
			ps = conn.prepareStatement(sbSql.toString());
			int index = 0;
			ps.setString(++index, "6");
			ps.setString(++index, "6");
			ps.setInt(++index, romnum);
			rs = ps.executeQuery();
			while (rs.next()) {
				acctNo = rs.getString("ACCT_NO");
			}
			// conn.rollback();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			this.close(new Statement[] { ps }, conn);
		}
		return acctNo;
	}

	/**
	 * insert cust and acct
	 * 
	 * @param acctNo
	 * @return
	 */
	public int insertCustAndAcct(String acctNo) {
		Connection conn = DsUtil.getInstance().getConnection();
		if (conn == null) {
			throw new RuntimeException(
					"Fatal error. Cann't get jdbc Connection...");
		}
		StringBuffer insertCust = new StringBuffer();
		insertCust.append("INSERT INTO AIP_CUST_INFO ( ");
		insertCust.append("	ACCT_NO ");
		insertCust.append("	,CUST_NAME ");
		insertCust.append("	,CUST_ABBR ");
		insertCust.append("	,CERT_TYPE_ID ");
		insertCust.append("	,CERT_NUM ");
		insertCust.append("	,ACCT_STAT ");
		insertCust.append("	,LOGIN_PWD ");
		insertCust.append("	,BRANCH_ID ");
		insertCust.append("	,GRADE_ID ");
		insertCust.append("	,BROKER_ID ");
		insertCust.append("	,FARE_MODEL_ID ");
		insertCust.append("	,AREA_CODE ");
		insertCust.append("	) ");
		insertCust.append("VALUES ( ");
		insertCust.append("	? ");
		insertCust.append("	,? ");
		insertCust.append("	,? ");
		insertCust.append("	,? ");
		insertCust.append("	,? ");
		insertCust.append("	,? ");
		insertCust.append("	,? ");
		insertCust.append("	,? ");
		insertCust.append("	,? ");
		insertCust.append("	,? ");
		insertCust.append("	,? ");
		insertCust.append("	,? ");
		insertCust.append("	) ");

		StringBuffer insertAcct = new StringBuffer();
		insertAcct
				.append("INSERT INTO AIP_ACCT_ACCOUNT (ACCT_NO, ACCOUNT_NO, SUB_ACCOUNT_NO, STAT, O_DATE, M_DATE) ");
		insertAcct.append("VALUES (?, ?, ?, ?, ?, ?)");

		Statement stmtLock = null;
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		int result = 0;
		try {
			conn.setAutoCommit(false);
			// Lock table
			stmtLock = conn.createStatement();
			// stmtLock.execute("lock table aip_cust_info in exclusive mode ");
			// // share row exclusive mode
			// stmtLock.execute("lock table aip_cust_info in share row exclusive mode");

			// Insert aip_cust_info
			ps1 = conn.prepareStatement(insertCust.toString());
			int index = 0;
			ps1.setString(++index, acctNo);
			ps1.setString(++index, "Noname");
			ps1.setString(++index, "Noname");
			ps1.setString(++index, "0");
			ps1.setString(++index, RandomUtil.randomString(18));
			ps1.setString(++index, "6");
			ps1.setString(++index, "5X");
			ps1.setString(++index, "ABCD9998");
			ps1.setString(++index, "0001");

			ps1.setString(++index, "xuhailin730");
			ps1.setString(++index, "F00001");
			ps1.setString(++index, "120000");
			result += ps1.executeUpdate();

			// INTO AIP_ACCT_ACCOUNT
			ps2 = conn.prepareStatement(insertAcct.toString());
			index = 0;
			String account = "6029071000119734";
			ps2.setString(++index, acctNo);
			ps2.setString(++index, account);
			ps2.setString(++index, null);
			ps2.setString(++index, "6");
			ps2.setString(++index, null);
			ps2.setString(++index, null);
			result += ps2.executeUpdate();
			// Thread.sleep(5000 * 3);
			// conn.rollback();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			this.close(new Statement[] { stmtLock, ps1, ps2 }, conn);
		}
		return result;
	}

	/**
	 * update Acct by acctNo
	 * 
	 * @param acctNo
	 * @return
	 */
	public int updateAcct(String acctNo) {
		Connection conn = DsUtil.getInstance().getConnection();
		if (conn == null) {
			throw new RuntimeException(
					"Fatal error. Cann't get jdbc Connection...");
		}

		String sql = "UPDATE AIP_ACCT_ACCOUNT T SET T.M_DATE = SYSDATE WHERE T.STAT = ? AND T.ACCT_NO = ?";

		PreparedStatement ps = null;
		int result = -1;
		try {
			conn.setAutoCommit(false);

			// update AIP_ACCT_ACCOUNT
			ps = conn.prepareStatement(sql);
			int index = 0;
			ps.setString(++index, "6");
			ps.setString(++index, acctNo);
			result = ps.executeUpdate();
			// conn.rollback();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			this.close(new Statement[] { ps }, conn);
		}
		return result;
	}

	/**
	 * delete cust and acct by acctNo
	 * 
	 * @param acctNo
	 * @return
	 */
	public int deleteCustAndAcct(String acctNo) {
		Connection conn = DsUtil.getInstance().getConnection();
		if (conn == null) {
			throw new RuntimeException(
					"Fatal error. Cann't get jdbc Connection...");
		}

		String sql1 = "DELETE FROM AIP_ACCT_ACCOUNT T WHERE T.ACCT_NO = ?";
		String sql2 = "DELETE FROM AIP_CUST_INFO T WHERE T.ACCT_NO = ?";
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		int result = 0;
		try {
			conn.setAutoCommit(false);

			// update AIP_ACCT_ACCOUNT
			ps1 = conn.prepareStatement(sql1);
			int index = 0;
			ps1.setString(++index, acctNo);
			result += ps1.executeUpdate();

			ps2 = conn.prepareStatement(sql2);
			index = 0;
			ps2.setString(++index, acctNo);
			result += ps2.executeUpdate();

			// conn.rollback();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			this.close(new Statement[] { ps1, ps2 }, conn);
		}
		return result;
	}

}
